"""
Database integration for the DingTalk Card Sender
Using SQLite for persistent storage
"""
import sqlite3
import json
import os
from datetime import datetime
from typing import Dict, List, Optional, Any
from contextlib import contextmanager


class DatabaseManager:
    """
    Manages database operations for the DingTalk Card Sender
    """
    
    def __init__(self, db_path: str = "ding_card_sender.db"):
        self.db_path = db_path
        self.init_db()
    
    def init_db(self):
        """
        Initialize the database with required tables
        """
        with self.get_db_connection() as conn:
            # Create users table
            conn.execute("""
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    username TEXT UNIQUE NOT NULL,
                    email TEXT,
                    hashed_password TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)
            
            # Create templates table
            conn.execute("""
                CREATE TABLE IF NOT EXISTS templates (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    user_id INTEGER NOT NULL,
                    content TEXT NOT NULL,  -- JSON content
                    category TEXT DEFAULT '默认分类',
                    tags TEXT,  -- JSON array of tags
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users (id)
                )
            """)
            
            # Create template versions table
            conn.execute("""
                CREATE TABLE IF NOT EXISTS template_versions (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    template_id INTEGER NOT NULL,
                    version_number INTEGER NOT NULL,
                    content TEXT NOT NULL,  -- JSON content
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (template_id) REFERENCES templates (id),
                    UNIQUE(template_id, version_number)
                )
            """)
            
            # Create send history table
            conn.execute("""
                CREATE TABLE IF NOT EXISTS send_history (
                    id TEXT PRIMARY KEY,
                    user_id INTEGER,
                    webhook_url TEXT,
                    content TEXT NOT NULL,  -- JSON content
                    result TEXT,  -- JSON result
                    msgtype TEXT,
                    status TEXT,  -- completed, failed
                    retries INTEGER DEFAULT 0,
                    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users (id)
                )
            """)
            
            # Create scheduled tasks table
            conn.execute("""
                CREATE TABLE IF NOT EXISTS scheduled_tasks (
                    id TEXT PRIMARY KEY,
                    user_id INTEGER,
                    scheduled_time TIMESTAMP NOT NULL,
                    webhook_url TEXT,
                    secret TEXT,
                    content TEXT NOT NULL,  -- JSON content
                    status TEXT DEFAULT 'scheduled',  -- scheduled, executing, completed, failed, cancelled
                    result TEXT,  -- JSON result
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    completed_at TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users (id)
                )
            """)
            
            # Create webhooks table
            conn.execute("""
                CREATE TABLE IF NOT EXISTS webhooks (
                    id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    webhook_id TEXT UNIQUE NOT NULL,
                    config TEXT NOT NULL,  -- JSON configuration
                    secret TEXT,
                    enabled BOOLEAN DEFAULT 1,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)
            
            conn.commit()
    
    @contextmanager
    def get_db_connection(self):
        """
        Context manager for database connections
        """
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row  # This allows us to access columns by name
        try:
            yield conn
        finally:
            conn.close()
    
    def save_user(self, username: str, email: str, hashed_password: str) -> Optional[int]:
        """
        Save a new user to the database
        """
        try:
            with self.get_db_connection() as conn:
                cursor = conn.execute(
                    "INSERT INTO users (username, email, hashed_password) VALUES (?, ?, ?)",
                    (username, email, hashed_password)
                )
                conn.commit()
                return cursor.lastrowid
        except sqlite3.IntegrityError:
            # Username already exists
            return None
    
    def get_user_by_username(self, username: str) -> Optional[Dict]:
        """
        Get a user by username
        """
        with self.get_db_connection() as conn:
            cursor = conn.execute(
                "SELECT id, username, email, hashed_password FROM users WHERE username = ?",
                (username,)
            )
            row = cursor.fetchone()
            if row:
                return dict(row)
            return None
    
    def save_template(self, user_id: int, name: str, content: Dict, category: str = "默认分类", tags: List[str] = None) -> bool:
        """
        Save a template to the database
        """
        if tags is None:
            tags = []
        
        try:
            with self.get_db_connection() as conn:
                # Check if template with this name already exists for the user
                cursor = conn.execute(
                    "SELECT id FROM templates WHERE name = ? AND user_id = ?",
                    (name, user_id)
                )
                existing = cursor.fetchone()
                
                content_json = json.dumps(content, ensure_ascii=False)
                tags_json = json.dumps(tags, ensure_ascii=False)
                
                if existing:
                    # Update existing template
                    conn.execute(
                        """UPDATE templates 
                           SET content = ?, category = ?, tags = ?, updated_at = CURRENT_TIMESTAMP 
                           WHERE name = ? AND user_id = ?""",
                        (content_json, category, tags_json, name, user_id)
                    )
                    template_id = existing[0]
                else:
                    # Insert new template
                    cursor = conn.execute(
                        """INSERT INTO templates (name, user_id, content, category, tags) 
                           VALUES (?, ?, ?, ?, ?)""",
                        (name, user_id, content_json, category, tags_json)
                    )
                    template_id = cursor.lastrowid
                
                # Create a new version
                self.create_template_version(template_id, content)
                
                conn.commit()
                return True
        except Exception as e:
            print(f"Error saving template: {e}")
            return False
    
    def create_template_version(self, template_id: int, content: Dict) -> bool:
        """
        Create a new version of a template
        """
        try:
            with self.get_db_connection() as conn:
                # Get the next version number
                cursor = conn.execute(
                    "SELECT COALESCE(MAX(version_number), 0) as max_version FROM template_versions WHERE template_id = ?",
                    (template_id,)
                )
                row = cursor.fetchone()
                next_version = row[0] + 1 if row[0] else 1
                
                content_json = json.dumps(content, ensure_ascii=False)
                
                conn.execute(
                    "INSERT INTO template_versions (template_id, version_number, content) VALUES (?, ?, ?)",
                    (template_id, next_version, content_json)
                )
                conn.commit()
                return True
        except Exception as e:
            print(f"Error creating template version: {e}")
            return False
    
    def get_template_by_name(self, user_id: int, name: str) -> Optional[Dict]:
        """
        Get a template by name and user ID
        """
        with self.get_db_connection() as conn:
            cursor = conn.execute(
                """SELECT id, name, content, category, tags, created_at, updated_at 
                   FROM templates WHERE name = ? AND user_id = ?""",
                (name, user_id)
            )
            row = cursor.fetchone()
            if row:
                result = dict(row)
                result["content"] = json.loads(result["content"])
                result["tags"] = json.loads(result["tags"]) if result["tags"] else []
                return result
            return None
    
    def get_user_templates(self, user_id: int) -> List[Dict]:
        """
        Get all templates for a user
        """
        with self.get_db_connection() as conn:
            cursor = conn.execute(
                """SELECT id, name, content, category, tags, created_at, updated_at 
                   FROM templates WHERE user_id = ?""",
                (user_id,)
            )
            rows = cursor.fetchall()
            
            templates = []
            for row in rows:
                template = dict(row)
                template["content"] = json.loads(template["content"])
                template["tags"] = json.loads(template["tags"]) if template["tags"] else []
                templates.append(template)
            
            return templates
    
    def delete_template(self, user_id: int, name: str) -> bool:
        """
        Delete a template
        """
        try:
            with self.get_db_connection() as conn:
                conn.execute(
                    "DELETE FROM templates WHERE name = ? AND user_id = ?",
                    (name, user_id)
                )
                conn.commit()
                return conn.total_changes > 0
        except Exception as e:
            print(f"Error deleting template: {e}")
            return False
    
    def get_template_versions(self, template_id: int) -> List[Dict]:
        """
        Get all versions of a template
        """
        with self.get_db_connection() as conn:
            cursor = conn.execute(
                """SELECT version_number, content, created_at 
                   FROM template_versions 
                   WHERE template_id = ? 
                   ORDER BY version_number DESC""",
                (template_id,)
            )
            rows = cursor.fetchall()
            
            versions = []
            for row in rows:
                version = dict(row)
                version["content"] = json.loads(version["content"])
                versions.append(version)
            
            return versions
    
    def save_send_history(self, user_id: Optional[int], webhook_url: str, content: Dict, result: Dict, 
                         msgtype: str, status: str = "completed", retries: int = 0, history_id: str = None) -> bool:
        """
        Save send history to database
        """
        try:
            import uuid
            if not history_id:
                history_id = str(uuid.uuid4())
            
            with self.get_db_connection() as conn:
                conn.execute(
                    """INSERT INTO send_history (id, user_id, webhook_url, content, result, msgtype, status, retries) 
                       VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
                    (
                        history_id, 
                        user_id, 
                        webhook_url, 
                        json.dumps(content, ensure_ascii=False),
                        json.dumps(result, ensure_ascii=False) if result else None,
                        msgtype,
                        status,
                        retries
                    )
                )
                conn.commit()
                return True
        except Exception as e:
            print(f"Error saving history: {e}")
            return False
    
    def get_send_history(self, user_id: Optional[int] = None) -> List[Dict]:
        """
        Get send history
        """
        with self.get_db_connection() as conn:
            if user_id:
                cursor = conn.execute(
                    """SELECT id, user_id, webhook_url, content, result, msgtype, status, retries, timestamp 
                       FROM send_history 
                       WHERE user_id = ? 
                       ORDER BY timestamp DESC""",
                    (user_id,)
                )
            else:
                cursor = conn.execute(
                    """SELECT id, user_id, webhook_url, content, result, msgtype, status, retries, timestamp 
                       FROM send_history 
                       ORDER BY timestamp DESC LIMIT 100"""
                )
            rows = cursor.fetchall()
            
            history = []
            for row in rows:
                record = dict(row)
                record["content"] = json.loads(record["content"])
                if record["result"]:
                    record["result"] = json.loads(record["result"])
                history.append(record)
            
            return history
    
    def get_user_by_session(self, session_id: str) -> Optional[Dict]:
        """
        Get user by session (would be implemented with sessions table)
        This is a placeholder for the actual session implementation
        """
        # This would be more complex in a real implementation
        # For now, return None to indicate this method needs more work
        pass
    
    def register_webhook(self, name: str, webhook_id: str, config: Dict, secret: str = None) -> bool:
        """
        Register a new webhook in the database
        """
        try:
            with self.get_db_connection() as conn:
                config_json = json.dumps(config, ensure_ascii=False)
                
                conn.execute(
                    """INSERT INTO webhooks (name, webhook_id, config, secret) 
                       VALUES (?, ?, ?, ?)""",
                    (name, webhook_id, config_json, secret)
                )
                conn.commit()
                return True
        except sqlite3.IntegrityError:
            # Webhook ID already exists
            return False
        except Exception as e:
            print(f"Error registering webhook: {e}")
            return False
    
    def get_webhook(self, webhook_id: str) -> Optional[Dict]:
        """
        Get webhook configuration by ID
        """
        with self.get_db_connection() as conn:
            cursor = conn.execute(
                "SELECT name, webhook_id, config, secret, enabled FROM webhooks WHERE webhook_id = ?",
                (webhook_id,)
            )
            row = cursor.fetchone()
            if row:
                result = dict(row)
                result["config"] = json.loads(result["config"])
                return result
            return None
    
    def save_scheduled_task(self, task_id: str, user_id: int, scheduled_time: str, webhook_url: str, 
                           secret: str, content: Dict) -> bool:
        """
        Save a scheduled task to the database
        """
        try:
            with self.get_db_connection() as conn:
                conn.execute(
                    """INSERT INTO scheduled_tasks 
                       (id, user_id, scheduled_time, webhook_url, secret, content) 
                       VALUES (?, ?, ?, ?, ?, ?)""",
                    (
                        task_id,
                        user_id,
                        scheduled_time,
                        webhook_url,
                        secret,
                        json.dumps(content, ensure_ascii=False)
                    )
                )
                conn.commit()
                return True
        except Exception as e:
            print(f"Error saving scheduled task: {e}")
            return False
    
    def get_scheduled_tasks(self, status: str = None) -> List[Dict]:
        """
        Get scheduled tasks, optionally filtered by status
        """
        with self.get_db_connection() as conn:
            if status:
                cursor = conn.execute(
                    """SELECT id, user_id, scheduled_time, webhook_url, secret, content, status, 
                              created_at, completed_at 
                       FROM scheduled_tasks 
                       WHERE status = ? 
                       ORDER BY scheduled_time""",
                    (status,)
                )
            else:
                cursor = conn.execute(
                    """SELECT id, user_id, scheduled_time, webhook_url, secret, content, status, 
                              created_at, completed_at 
                       FROM scheduled_tasks 
                       ORDER BY scheduled_time"""
                )
            rows = cursor.fetchall()
            
            tasks = []
            for row in rows:
                task = dict(row)
                task["content"] = json.loads(task["content"])
                tasks.append(task)
            
            return tasks
    
    def update_scheduled_task_status(self, task_id: str, status: str, result: Dict = None) -> bool:
        """
        Update the status of a scheduled task
        """
        try:
            with self.get_db_connection() as conn:
                if result:
                    result_json = json.dumps(result, ensure_ascii=False)
                    conn.execute(
                        """UPDATE scheduled_tasks 
                           SET status = ?, result = ?, completed_at = CURRENT_TIMESTAMP 
                           WHERE id = ?""",
                        (status, result_json, task_id)
                    )
                else:
                    conn.execute(
                        """UPDATE scheduled_tasks 
                           SET status = ?, completed_at = CURRENT_TIMESTAMP 
                           WHERE id = ?""",
                        (status, task_id)
                    )
                conn.commit()
                return True
        except Exception as e:
            print(f"Error updating scheduled task: {e}")
            return False


# Global database instance
db_manager = DatabaseManager()